﻿/* Tạo ra database NHANVIEN_DUAN */
CREATE DATABASE NHANVIEN_DUAN
GO

/* Khai báo sử dụng database */
USE NHANVIEN_DUAN
GO

/* Tạo ra các bảng quan hệ */
--1. Công ty
CREATE TABLE CONGTY
(
	MaCongTy varchar(15) PRIMARY KEY NOT NULL,
	TenCongTy nvarchar(50),
	DienThoai varchar(15),
	Website varchar(30)
)
GO

--2. Chi nhánh
CREATE TABLE CHINHANH
(
	MaChiNhanh varchar(15) PRIMARY KEY NOT NULL,
	TenChiNhanh nvarchar(50),
	DienThoai varchar(15),
	MaCongTy varchar(15),
	CONSTRAINT FK_CHINHANH_CONGTY FOREIGN KEY(MaCongTy)
									REFERENCES CONGTY(MaCongTy)
)
GO
--3. Dự án
CREATE TABLE DUAN
(
	MaDuAn varchar(15) PRIMARY KEY NOT NULL,
	TenDuAn nvarchar(50),
	NgayThamGia datetime,
	TinhTrang bit
)
GO
--4. Nhân viên
CREATE TABLE NHANVIEN
(
	MaNhanVien varchar(15) PRIMARY KEY NOT NULL,
	TenNhanVien nvarchar(50),
	Ngaysinh datetime,
	Email varchar(30),
	MaChiNhanh varchar(15),
	MaDuAn varchar(15),
	CONSTRAINT FK_NHANVIEN_DUAN FOREIGN KEY(MaDuAn)
								REFERENCES DUAN(MaDuAn),
	CONSTRAINT FK_NHANVIEN_CHINHANH FOREIGN KEY(MaChiNhanh)
								REFERENCES CHINHANH(MaChiNhanh)
)
GO

--1.Tìm tên các nhân viên của chi nhánh Hà Nội làm việc cho dự án A, xếp theo tên nhân viên:
SELECT MaNhanVien,TenNhanVien,Ngaysinh,Email
FROM NHANVIEN
WHERE MaChiNhanh IN (SELECT MaChiNhanh FROM CHINHANH 
					WHERE TenChiNhanh=N'Hà Nội')
	AND
		MaDuAn=(SELECT MaDuAn FROM DUAN
					WHERE TenDuAn=N'A')
ORDER BY TenNhanVien

--2. Tìm các dự án mà chi nhánh Hà Nội có tham gia, xếp theo thời gian mới nhất trước:
SELECT *FROM DUAN
WHERE MaDuAn IN (SELECT MaDuAn FROM NHANVIEN
			  INNER JOIN CHINHANH
			  ON NHANVIEN.MaChiNhanh=CHINHANH.MaChiNhanh
			  WHERE TenChiNhanh=N'Hà Nội')
ORDER BY NgayThamGia DESC